﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace CSharp
{
    //DBHelper类封装对数据库的操作，简化ADO.NET的调用
    //将固定的没有变化的部分放入DBHelper，使用方法封装ExecuteNonQurey
    class DBHelper
    {
        //将连接字符串放入DBHelper，不会让ADO.NET到处都是连接字符串
        private const string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;
                                    Initial Catalog=18bang;Integrated Security=True";

        //有时候会将连接对象也放入DBHelper，一般使用属性
        //public IDbConnection connection 
        //{
        //    get 
        //    {
        //        return new SqlConnection(connectionString);
        //    }
        //}
        //使用方法
        public IDbConnection GetNewconnection()
        {
            return new SqlConnection(connectionString);
        }

        private int ExecuteNonQurey(IDbCommand command)
        {
            using (IDbConnection connection = GetNewconnection())
            {
                try
                {
                    connection.Open();
                    command.Connection = connection;
                    return command.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }
            };
        }
        public int Update(IDbCommand command)
        {
            return ExecuteNonQurey(command);
        }
        
        //把Id为10,12,14的ShortStudent删除  
        public int Delete(IDbCommand command)
        {
            return ExecuteNonQurey(command);
        }
        public void DeleteRange(IDbCommand[] commands)
        {
            using (IDbConnection connection = GetNewconnection())
            {
                connection.Open();
                for (int i = 0; i < commands.Length; i++)
                {
                    commands[i].Connection = connection;
                    commands[i].ExecuteNonQuery();
                }
            }
        }
        //如果NonQuery里面有多个参数，用字符串拼接容易出现SQL注入，所以需要IDateParameter输入多个参数（参数化查询）
        //private int ExecuteNonQurey(string cmdText, params IDataParameter[] parameters)
        //{
        //    IDbCommand command = new SqlCommand();
        //    command.CommandText = cmdText;
        //    for (int i = 0; i < parameters.Length; i++)
        //    {
        //        command.Parameters.Add(parameters[i]);

        //    }
        //    return ExecuteNonQurey(command);
        //}
        //cmdText 和parameters都是基于command

        //public int Delete(string cmdText, params IDataParameter[] parameters)
        //{
        //    return ExecuteNonQurey(cmdText, parameters);
        //}
        //public int Insert(string cmdText, params IDataParameter[] parameters)
        //{
        //    return ExecuteNonQurey(cmdText, parameters);
        //}

    }
}
